﻿using ChuanYe.Core.Utils;
using ChuanYe.CountyFireManagement.EF.MySQL;
using ChuanYe.CountyFireManagement.EF.MySQL.Models;
using ChuanYe.CountyFireManagement.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ChuanYe.CountyFireManagement.BLL
{
    public class FireStatisticsBLL
    {
        #region 构造单例
        private FireStatisticsBLL() { }
        private static FireStatisticsBLL _instance;
        public static FireStatisticsBLL Instance
        {
            get
            {
                return _instance ?? (_instance = new FireStatisticsBLL());
            }
        }


        /// <summary>
        /// 区县级别 统计
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<TVAddressStatisticsModel> TVAddressStatistics(string addressCounty,DateTime? startTime, DateTime? endTime)
        {
            using (CountyFireManagementContext ef = new CountyFireManagementContext())
            {
                var sql = AppSetting.GetConfig("Sql:FireStatisticsBLL.TVAddressStatistics");
                string paramsSql = string.Empty;
                if (!string.IsNullOrEmpty(addressCounty))
                {
                    paramsSql += $" and  b.AddressCounty='{addressCounty}' ";
                }
                if (startTime.HasValue)
                {
                    paramsSql += $" and  a.TransDatetime>='{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (endTime.HasValue)
                {
                    paramsSql += $" and a.TransDatetime<='{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                paramsSql += " and b.AddressCounty!=''  ";
                paramsSql += " group by b.AddressCounty,b.AddressTown,AddressVillage ";
                sql = string.Format(sql,paramsSql);
                var list = ef.Database.SqlQuery<TVAddressStatisticsModel>(sql);
                return list;
            }

           
        }

        public List<TVTimetatisticsModel> TVTimeStatistics(string addressCounty, DateTime? startTime, DateTime? endTime)
        {
            using (CountyFireManagementContext ef = new CountyFireManagementContext())
            {
                var sql = " select a.TransDatetime from firefinalreport as a left join  fireinfo as b on a.FireID=b.FireID where 1=1 {0}";
                string paramsSql = string.Empty;
                if (!string.IsNullOrEmpty(addressCounty))
                {
                    paramsSql += $" and  b.AddressCounty='{addressCounty}' ";
                }
                if (startTime.HasValue)
                {
                    paramsSql += $" and  a.TransDatetime>='{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (endTime.HasValue)
                {
                    paramsSql += $" and a.TransDatetime<='{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                paramsSql += " and b.AddressCounty!='' ";
                sql = string.Format(sql, paramsSql);
                var dateTimes = ef.Database.SqlQuery<TVTimetatisticsModelSub>(sql);
                var list = dateTimes.GroupBy(g => new { g.TransDatetime.Hour })
                                    .Select(s => new TVTimetatisticsModel { Hour=s.Key.Hour, Count = s.Count() }).ToList();
                return list;
            }
        }

        public List<TVAlarmTypeStatisticsModel> TVAlarmTypeStatistics(string addressCounty, DateTime? startTime, DateTime? endTime)
        {
            using (CountyFireManagementContext ef = new CountyFireManagementContext())
            {
                var sql = " select a.AlarmTypeID,c.TypeName as AlarmTypeName,COUNT(1) as 'Count' from firefinalreport as a ";
                sql += " left join  fireinfo as b on a.FireID=b.FireID  ";
                sql += " left join firealarmtype as c on a.AlarmTypeID =c.TypeID   ";
                sql += " where 1=1 {0}";
                sql += " group by a.AlarmTypeID ";
                string paramsSql = string.Empty;
                if (!string.IsNullOrEmpty(addressCounty))
                {
                    paramsSql += $" and  b.AddressCounty='{addressCounty}' ";
                }
                if (startTime.HasValue)
                {
                    paramsSql += $" and  a.TransDatetime>='{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (endTime.HasValue)
                {
                    paramsSql += $" and a.TransDatetime<='{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                paramsSql += " and b.AddressCounty!='' ";
                sql = string.Format(sql, paramsSql);
                var list = ef.Database.SqlQuery<TVAlarmTypeStatisticsModel>(sql);
                return list;
            }
        }

        public List<TVCauseStatisticsModel> TVCauseStatistics(string addressCounty, DateTime? startTime, DateTime? endTime)
        {
            using (CountyFireManagementContext ef = new CountyFireManagementContext())
            {
                var sql = " select a.CauseID,c.CauseName,COUNT(1) as 'Count' from firefinalreport as a ";
                sql += " left join  fireinfo as b on a.FireID=b.FireID  ";
                sql += " left join firecause as c on a.CauseID=c.CauseID ";
                sql += " where 1=1 {0}";
                sql += " group by a.AlarmTypeID ";
                string paramsSql = string.Empty;
                if (!string.IsNullOrEmpty(addressCounty))
                {
                    paramsSql += $" and  b.AddressCounty='{addressCounty}' ";
                }
                if (startTime.HasValue)
                {
                    paramsSql += $" and  a.TransDatetime>='{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (endTime.HasValue)
                {
                    paramsSql += $" and a.TransDatetime<='{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                paramsSql += " and b.AddressCounty!='' ";
                sql = string.Format(sql, paramsSql);
                var list = ef.Database.SqlQuery<TVCauseStatisticsModel>(sql);
                return list;
            }
        }

        public object TVSummaryStatistics(string addressCounty, DateTime? startTime, DateTime? endTime)
        {
            return null;
        }



        #endregion
    }
}
